import mysql.connector  
from mysql.connector import Error  
# 数据库配置  
config = {  
    'user': 'your_user',  
    'password': 'your_password',  
    'host': 'your_host',  
    'database': 'attendance_system',  
    'raise_on_warnings': True  
}  
class adminDatabase:  
    def __init__(self, config=config):  
        self.config = config  
        self.connection = None  
        self.cursor = None  
  
    def connect(self):  
        try:  
            self.connection = mysql.connector.connect(**self.config)  
            self.cursor = self.connection.cursor()  
            print("Connected to MySQL database successfully.")  
        except Error as e:  
            print(f"Error while connecting to MySQL: {e}")  
  
    def close(self):  
        if self.connection.is_connected():  
            self.cursor.close()  
            self.connection.close()  
            print("MySQL connection is closed.")  
  
    def create_table(self):  
        create_table_sql = """  
        CREATE TABLE IF NOT EXISTS administrator (  
            id INT AUTO_INCREMENT PRIMARY KEY,  
            employee_id VARCHAR(255) UNIQUE NOT NULL,  
            name VARCHAR(255) NOT NULL,  
            password VARCHAR(255) NOT NULL  
        );  
        """  
        try:  
            self.cursor.execute(create_table_sql)  
            self.connection.commit()  
            print("Table created successfully.")  
        except Error as e:  
            print(f"Error while creating table: {e}")  
  
    def insert_employee(self, employee_id, name, password):  
        insert_sql = """  
        INSERT INTO administrator (employee_id, name, password)  
        VALUES (%s, %s, %s);  
        """  
        try:  
            self.cursor.execute(insert_sql, (employee_id, name, password))  
            self.connection.commit()  
            print(f"Administrator {name} with ID {employee_id} added successfully.")  
        except Error as e:  
            print(f"Error while adding administrator: {e}")  
    #查询所有人的信息
    def select_all_employees(self):  
        select_all_sql = """  
        SELECT * FROM administrator;  
        """  
        try:  
            self.cursor.execute(select_all_sql)  
            results = self.cursor.fetchall()  
            for row in results:  
                print(f"ID: {row[0]}, 工号: {row[1]}, 姓名: {row[2]}, 密码: {row[3]}")  
        except Error as e:  
            print(f"Error while fetching data: {e}")
        return results #返回结果  
   #通过工号查询信息
    def select_employee_by_id(self, employee_id):  
        select_by_id_sql = """  
        SELECT * FROM administrator WHERE employee_id = %s;  
        """  
        try:  
            self.cursor.execute(select_by_id_sql, (employee_id,))  
            result = self.cursor.fetchone()  
            if result:  
                print(f"Found employee with ID: {result[0]}, 工号: {result[1]}, 姓名: {result[2]}, 密码: {result[3]}")  
            else:  
                print(f"Employee with ID {employee_id} not found.")  
        except Error as e:  
            print(f"Error while fetching data: {e}")
        return result  
'''
  
#测试
# 创建EmployeeDatabase对象并连接数据库  
db = adminDatabase()  
db.connect()  
  
# 创建表（如果表不存在）  
db.create_table()  
  
# 插入一些示例数据  
db.insert_employee('E001', 'Alice', 'password123')  
db.insert_employee('E002', 'Bob', 'securepass')  
  
# 查询所有员工信息    
results = db.select_all_employees()  
print(results) #列表中的元组
# 根据工号查询员工信息  
result = db.select_employee_by_id('E001')  
print(result[1])
# 关闭数据库连接  
db.close()
'''
